package dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.Serializable;
import java.security.interfaces.RSAKey;
import java.security.spec.ECField;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.elasticsearch.index.query.MatchAllQueryBuilder;
import org.elasticsearch.index.query.QueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.carrotsearch.hppc.IntIndexedContainer;
import com.els.ElsConnection;
import com.els.factory.ElasticFactory;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonNull;
import com.google.gson.JsonObject;
import com.webapp.dao.CounselorDAO;
import com.webapp.dao.CounselorDetailDAO;
import com.webapp.dao.UserDetailDAO;
import com.webapp.domain.entity.AssessmentData;
import com.webapp.domain.entity.Counselor;
import com.webapp.domain.entity.CounselorDetail;
import com.webapp.domain.entity.UserDetail;
/**
 * 咨询师导入与修改
 * @author administrator
 *
 */
//@RunWith(SpringJUnit4ClassRunner.class)   
//@ContextConfiguration(locations={"classpath:spring/spring-applicationContext.xml","classpath:spring/spring-servlet.xml"})

public class CounselorInsert {

	  @Autowired
	  CounselorDAO counselorDAO;
	  @Autowired
	  CounselorDetailDAO counselorDetailDAO;
	  @Autowired
	  UserDetailDAO userDetailDao;
//	  @Autowired
//	  CounselorDetailDAO counselorDetailDao
	  @Autowired  
	    SessionFactory sessionFactory; 
	  public Session getSession(){
	    	return sessionFactory.openSession();
	    }
	    
	  
	  @Test
	  public void insertCounselor() throws Exception{
//		  String imgurl = "C:\\Users\\Administrator\\Desktop\\odr\\新增咨询师（公证员）3.14\\新增咨询师（公证员）3.14\\img\\";
//		  File imgFile=new File("C:\\Users\\Administrator\\Desktop\\odr\\ODR服务资源数据\\司法局咨询师——杨丽\\咨询师照片-司法局\\咨询师照片-司法局\\");
		  File file=new File("/Users/HongweiTong/Downloads/新增两位咨询师.xlsx");
		  FileInputStream is=new FileInputStream(file);
		  XSSFWorkbook xxx = new XSSFWorkbook(is); 
		  XSSFSheet sheet = xxx.getSheetAt(0);
		  int trlength=sheet.getLastRowNum();//总行数
		   Counselor counselor = null;
		   CounselorDetail counselorDetail = null;
//	   		HSSFRow row=sheet.getRow(0);
		  XSSFRow row=sheet.getRow(1);
//	   		int tdlength=row.getLastCellNum();//总列数
		  JsonObject jsonObject = null;
		  Session session = getSession();
		  for (int i = 1; i < trlength+1; i++) {	
			  //XSSFCell cell=sheet.getRow(i).getCell(13);
			  XSSFRow row2=sheet.getRow(i);
//	   			XSSFCell cell = sheet.getRow(i).getCell(1);
			  	  //String xh = row2.getCell(0).toString();
			  	  //xh = xh.substring(0, xh.indexOf(".")); 
			  	  //String name = row2.getCell(1).toString();
				   counselor = new Counselor();
				  counselorDetail = new CounselorDetail();
				  counselor.setCreateDate(new Date());
				  counselor.setIsLogin("0");
				  
				  counselor.setName(PinyinUtil.getPinYin(row2.getCell(1).toString()));
				  counselorDetail.setName(counselor.getName());
				  counselorDetail.setActualName(row2.getCell(1).toString());
				  counselorDetail.setSex(row2.getCell(2)==null?"":row2.getCell(2).toString());
				  counselorDetail.setBirth(parseExcel(row2.getCell(3)));//日期格式转换
				  
				  counselor.setPassword("xihu");
				  
				  counselorDetail.setArea(row2.getCell(8)==null?"":row2.getCell(8).toString() + (row2.getCell(9)==null?"":row2.getCell(9).toString()) + (row2.getCell(10)==null?"":row2.getCell(10).toString()));
				 
				  counselorDetail.setAbility(row2.getCell(7)==null?"":row2.getCell(7).toString());
				  counselorDetail.setAddress(row2.getCell(11)==null?"":row2.getCell(11).toString());
				  counselorDetail.setPhone(getCell(row2.getCell(12)));//手机号数字类型处理
				  //counselor.setEmail(row2.getCell(14)==null?"":row2.getCell(12).toString());//邮箱
				  counselorDetail.setCounselor(counselor);
//		   		  long id = (Long) counselorDetailDAO.save(counselorDetail);
		   		long id = (Long)session.save(counselorDetail);
//				  long id = 0l;
				  //String imgName = updataImgName(imgurl,xh+name, counselor.getId());图片名字
				  System.out.println(id+"----------");
				  jsonObject = new JsonObject();
				  jsonObject.addProperty("name", row2.getCell(1).toString());//姓名
				  jsonObject.addProperty("sex", row2.getCell(2)==null?"":row2.getCell(2).toString());//性别
				  jsonObject.addProperty("birth",parseExcel(row2.getCell(3)));//生日
				  jsonObject.addProperty("education", row2.getCell(4)==null?"":row2.getCell(4).toString());//文化程度
				  jsonObject.addProperty("role", row2.getCell(5)==null?"":row2.getCell(5).toString());//职业
				  jsonObject.addProperty("jobstart",parseExcel( row2.getCell(6)));//开始从业时间
				  if (row2.getCell(7)==null) {
					  jsonObject.addProperty("ability", "");//专业范围
				  }else{
					  jsonObject.add("ability", stringToJsonArray(row2.getCell(7).toString()));//专业范围
				  }
				  jsonObject.addProperty("province", row2.getCell(8)==null?"":row2.getCell(8).toString());//所属省
				  jsonObject.addProperty("city", row2.getCell(9)==null?"":row2.getCell(9).toString());//所属市
				  jsonObject.addProperty("region", row2.getCell(10)==null?"":row2.getCell(10).toString());//所属区
				  jsonObject.addProperty("area", (row2.getCell(8)==null?"":row2.getCell(8).toString()) + (row2.getCell(9)==null?"":row2.getCell(9).toString()) + (row2.getCell(10)==null?"":row2.getCell(10).toString()));//所属区域
				  jsonObject.addProperty("address", row2.getCell(11)==null?"":row2.getCell(11).toString());//详细地址
				  jsonObject.addProperty("phone", getCell(row2.getCell(12)));//手机号码
				  jsonObject.addProperty("job", row2.getCell(13)==null?"":row2.getCell(13).toString());//工作单位
				  jsonObject.addProperty("resume", row2.getCell(14)==null?"":row2.getCell(14).toString());//个人简介
				  //jsonObject.addProperty("weixin", row2.getCell(14)==null?"":row2.getCell(14).toString());//微信
				  //jsonObject.addProperty("email", row2.getCell(15)==null?"":row2.getCell(15).toString());//个人邮箱
				  //jsonObject.addProperty("professionalCard", row2.getCell(16)==null?"":row2.getCell(16).toString());//职业证号
				  jsonObject.addProperty("photo", "zixunshi_"+counselor.getId());
				  jsonObject.addProperty("status", "0");  //公益和有偿
				  jsonObject.addProperty("id", counselor.getId());
				  ElsConnection ec = ElasticFactory.getConnection();
				  String _id = ec.insertOne("xxx", "xxx", null, jsonObject);
				  System.err.println(_id);
		  }
		  
	  }
	  
  @Test  //修改数据库中咨询师area
	  public void changearea(){
		  ElsConnection ec = ElasticFactory.getConnection();
		  JsonObject name = new JsonObject();
		  name.addProperty("url", "https://www.baidu.com/");
		  JsonObject _id = ec.selectOne("zzz", "zzz", null, name);
		  String id = _id.get("_id").getAsString();
		  _id.remove("_id");
		  _id.remove("_type");
		  _id.addProperty("photo", "x4.png");
		  _id.addProperty("url", "http://hangzhou.zjcourt.cn");
		  String iid = ec.insertOne("zzz", "zzz",id , _id);
		  System.err.println(iid);
	  }
  
  @Test  //修改数据库中咨询师area
  public void removeitem(){
	  ElsConnection ec = ElasticFactory.getConnection();
	  JsonObject name = new JsonObject();
	  name.addProperty("name", "test");
	  JsonObject _id = ec.selectOne("xxx", "xxx", null, name);
	  if(_id!=null)
	  {	String id = _id.get("_id").getAsString();
	  	ec.deleteOne("xxx", "xxx", id); System.out.println("delete");}
	  else {
		  System.out.println("null");
	}
  }
  
  @Test  //分开area省市区
  
  	 public void divided(){
	  
	     ElsConnection ec = ElasticFactory.getConnection();
	     QueryBuilder queryBuilder = QueryBuilders.matchAllQuery();
	     List<JsonObject> list = ec.queryByQueryBuilder("xxx", "xxx", queryBuilder, 0,2000);
	     for(int n=0; n<list.size(); n++)
	     {
	    	 if(list.get(n).get("area") == null  ){
	    		 System.out.println("ddddd");
	    		 continue;
	    	 }
	    	 String s = list.get(n).get("area").getAsString();
	    	 String id =list.get(n).get("_id").getAsString();
	    	 list.get(n).remove("_id");
	    	 list.get(n).remove("_type");
	    	 
	    	 int[] a = {0,1,2};
	 	     int[] b = new int[4];
	 	     b[0] = -1;
	 	     b[1] = s.indexOf("区");
	 	     b[2] = s.indexOf("市");
	 	     b[3] = s.indexOf("省"); 
	 	     for(int i=1; i<=3; i++)
	 	     {
	 	     	for(int j=i+1;j<=3;j++)
	 	    	{
	 	    		if(b[i] > b[j])
	 	    		{
	 	    			int tem = b[i];
	 	    			b[i] = b[j];
	 	    			b[j] = tem;
	 	    			int temx = a[i-1];
	 	    			a[i-1] = a[j-1];
	 	    			a[j-1] = temx;
	 	    		}
	 	    	}
	 	     }
	 	     
	 	     String l = null ,m = null,f = null;
	 	     for(int i=1; i<=3; i++)
	 	     {
	 	    	switch (a[i-1])
	 	    	{
	 			case 0:
	 				l = s.substring(b[i-1]+1,b[i]+1);
	 				if( l != null && l.length() > 0)
	 			 		 list.get(n).addProperty("region", l);
	 				break;
	 			case 1:
	 				m = s.substring(b[i-1]+1,b[i]+1);
	 				if( m != null && m.length() > 0) 
	 			 		 list.get(n).addProperty("city", m);
	 				break;
	 			case 2:
	 				f = s.substring(b[i-1]+1,b[i]+1);
	 				if( f != null && f.length() > 0) 
	 			 		 list.get(n).addProperty("province", f);
	 				break;
	 			default:
	 				break;
	 			}
	 	     } 
	 	    if( (f == null||f.length()<=0)  && m != null)
	 	    {
	 	    	 list.get(n).addProperty("province", m);
	 	    }
		 	 String iid = ec.insertOne("xxx", "xxx",id , list.get(n));
		 	 System.err.println(iid);
		 	 System.out.println(n);
	     }
  }
	  
	  /**
	   * 格式化专业范围
	   * @param string
	   * @return
	   */
	  public static JsonArray StringToJsonArray(String string){
			String[] array=string.split("、");	
			StringBuffer buffer=new StringBuffer();
			buffer.append("[");
			for (int k = 0; k < array.length; k++) {
				array[k]="'"+array[k]+"'";
			}
			for (int m = 0; m < array.length; m++) {
				buffer.append(array[m]);
				if(m!=array.length-1){
					buffer.append(",");
				}
			}
			buffer.append("]");
			 JsonArray jsonArray = (JsonArray) new Gson().fromJson(buffer.toString(), JsonArray.class);
		  return jsonArray;
	  }
	  
	  
	  /**
	   * 修改图片名字
	   * @param imgPath
	   * @param id
	   * @return
	   */
	  public static String updataImgName(String imgPath,String index,Long id){
		 
		  File imgFile = new File(imgPath);
		  File[] imgs = imgFile.listFiles();
		  for (File file : imgs) {
			String imgName = file.getName();
			if (imgName.startsWith(index)) {
				String end = imgName.substring(imgName.lastIndexOf("."));
				file.renameTo(new File(imgPath+"zixunshi_"+id+end));
				return "zixunshi_"+id+end;
			}
		  }
		  return null;
	  }
	  private String parseExcel(Cell cell) { 
		    if (cell==null) {
				return "";
			}
	        String result = new String();  
	        switch (cell.getCellType()) {  
	        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
	            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式  
	                SimpleDateFormat sdf = null;  
	                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat  
	                        .getBuiltinFormat("h:mm")) {  
	                    sdf = new SimpleDateFormat("HH:mm");  
	                } else {// 日期  
	                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
	                }  
	                Date date = cell.getDateCellValue();  
	                result = sdf.format(date);  
	            } else if (true) {  //cell.getCellStyle().getDataFormat() == 58
	                // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)  
	                SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");  
	                double value = cell.getNumericCellValue();  
	                Date date = org.apache.poi.ss.usermodel.DateUtil  
	                        .getJavaDate(value);  
	                result = sdf.format(date);  
	            } else {  
	                double value = cell.getNumericCellValue();  
	                CellStyle style = cell.getCellStyle();  
	                DecimalFormat format = new DecimalFormat();  
	                String temp = style.getDataFormatString();  
	                // 单元格设置成常规  
	                if (temp.equals("General")) {  
	                    format.applyPattern("#");  
	                }  
	                result = format.format(value);  
	            }  
	            break;  
	        case HSSFCell.CELL_TYPE_STRING:// String类型  
	            result = cell.getRichStringCellValue().toString();  
	            break;  
	        case HSSFCell.CELL_TYPE_BLANK:  
	            result = "";  
	        default:  
	            result = "";  
	            break;  
	        }  
	        return result;  
	    } 
	  public String getCell(Cell cell) {
			DecimalFormat df = new DecimalFormat("#");
			if (cell == null)
				return "";
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
				if(HSSFDateUtil.isCellDateFormatted(cell)){
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
				}
				return df.format(cell.getNumericCellValue());
			case HSSFCell.CELL_TYPE_STRING:
				System.out.println(cell.getStringCellValue());
				return cell.getStringCellValue();
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case HSSFCell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case HSSFCell.CELL_TYPE_ERROR:
				return cell.getErrorCellValue() + "";
			}
			return "";
		}
	  
	  
	  
	  
	  
	  @Test
	  public void uodate(){
		  String indexName ="xxx";
		  ElsConnection ec = ElasticFactory.getConnection();
//		  String id = "AVo73ea1HFp9vHOxLX3L";
//		  JsonObject jsonObject = ec.selectOne(indexName, indexName, id, null);
//		  String str = "['涉外民商事','知识产权']";
//		  JsonArray array = new Gson().fromJson(str, JsonArray.class);
//		  jsonObject.add("ability", array);
//		  jsonObject.remove("_id");
//		  jsonObject.remove("_type");
//		  String xx = ec.insertOne(indexName, indexName, id, jsonObject);
//		  System.err.println(xx);
		  
		  
		  boolean is = ec.deleteOne(indexName, indexName, "AVomo4jpHFp9vHOxLX2r");
		  System.err.println(is);
		  
	  }
//	  5
////	  6
//	  17
//	  37
//	  39
//	  42
//	  43
//	  44
//	  46
//	  47
//	  48
//	  49
//	  50
//	  52
//	  53
//	  56
//	  59
//	  63
//	  65
//	  66
//	  84
//	  86
//	  87
	  
	  @Test
	  public void xxxxxxx(){
		 List<Counselor> list = counselorDAO.loadAll();
		 String indexName = "xxx";
		 ElsConnection ec = ElasticFactory.getConnection();
		 MatchAllQueryBuilder queryBuilder = QueryBuilders.matchAllQuery();
		 List<JsonObject> ss = ec.queryAllByQueryBuilder(indexName, indexName, queryBuilder);
//		 for (JsonObject jsonObject : ss) {
//			String name = jsonObject.get("name").getAsString();
//			String id = jsonObject.get("id").getAsString();
//			System.err.println(name+"=="+id);
//		}
//		 int x = 1;
//		 for (JsonObject jsonObject : ss) {
//			 boolean is = true;
//			for (Counselor c : list) {
//				String id = String.valueOf(c.getId());
//				String _id = jsonObject.get("id").getAsString();
//				if (id.equals(_id)) {
//					System.err.println(x);
//					x++;
//					is = false;
//					break;
//				}
//			}
//			 
//			if (is) {
//				System.err.println(jsonObject.get("id").getAsString());
//			} 
//			 
//		 }
		 
		 for (Counselor c : list) {
			 String id = String.valueOf(c.getId());
			 boolean is = true;
			 for (JsonObject jsonObject : ss) {
				 String _id = jsonObject.get("id").getAsString();
				 if (id.equals(_id)) {
					is = false;
				}
			}
			 if (is) {
				 System.err.println(id);
				
			}
		}
		 
		 
		 
		 
	  }
	  
	/*  
	  @Test
	  public void insert(){
		  String id = "5";
		  JsonObject jsonObject = new JsonObject();
 			jsonObject.addProperty("name","han");
 			jsonObject.addProperty("sex", row2.getCell(2).toString());
 			jsonObject.addProperty("birth", row2.getCell(3).toString());
 			jsonObject.addProperty("education", row2.getCell(4).toString());
 			jsonObject.addProperty("role", row2.getCell(5).toString());
 			jsonObject.addProperty("jobstart", row2.getCell(6).toString());
 			jsonObject.addProperty("ability", row2.getCell(7).toString());
 			jsonObject.addProperty("area", "浙江省");
 			jsonObject.addProperty("address", row2.getCell(9).toString());
 			jsonObject.addProperty("phone", row2.getCell(10).toString());
 			jsonObject.addProperty("job", row2.getCell(11).toString());
 			jsonObject.addProperty("resume", row2.getCell(12).toString());
 			jsonObject.addProperty("photo", "zixunshi_"+id);
 			jsonObject.addProperty("id", id);
 			ElsConnection ec = ElasticFactory.getConnection();
 			String _id = ec.insertOne("xxx", "xxx", null, jsonObject);
 			System.err.println(_id);
	  }
	  
	  */
	  
	  @Test
	  public void insert(){
		 String indexName = "xxx";
		 ElsConnection ec = ElasticFactory.getConnection();
		 MatchAllQueryBuilder query = QueryBuilders.matchAllQuery();
		 List<JsonObject> list = ec.queryAllByQueryBuilder(indexName, indexName, query);
		 System.err.println(list.size());
		 List<String> strings = new ArrayList<String>();
 		 for (int i = 0; i < list.size(); i++) {
			 String id = list.get(i).get("id").getAsString();
			 strings.add(id);
		 }
 		 Set<String> set = new HashSet<String>(strings);
 		 System.err.println(set.size());
 		 for (String s : set) {
 			 int i = 1;
			for (String l : strings) {
				if (s.equals(l)) {
					i++;
					if (i==3) {
						System.err.println(s);
					}
				}
			}
		}
	  }
	  @Test
	  public void delete(){
		  String[] str = new String[]{
				  "79",
				  "78",
				  "41",
				  "40",
				  "82",
				  "83",
				  "80",
				  "81",
				  "67",
				  "68",
				  "7",
				  "70",
				  "8",
				  "58",
				  "57",
				  "18",
				  "64",
				  "62",
				  "54",
				  "88"
		  };
		  String indexName = "xxx";
		  System.err.println(str.length);
		  for (String string : str) {
			  QueryBuilder queryBuilder = QueryBuilders.termQuery("id", string);
			 
			  ElsConnection ec = ElasticFactory.getConnection();
			 List<JsonObject> list = ec.queryAllByQueryBuilder("xxx", "xxx", queryBuilder);
			 for (JsonObject jsonObject : list) {
				 if (jsonObject.get("role").getAsString().equals("咨询师")) {
					System.err.println(jsonObject.get("_id").getAsString()+"==="+string);
					boolean is = ec.deleteOne(indexName, indexName, jsonObject.get("_id").getAsString());
					System.out.println(is);
				}
				
			}
		}
		  
		  
	  }
	  @Test
	  public void ccccc(){
		  String[] str = new String[]{"5","17","37","39","42","43","44","46",
				  "47","48","49","50","52","53","56","59","63","65","66","84","86","87"
		  };
		  String indexName = "xxx";
		  System.err.println(str.length);
		  for (String string : str) {
			  QueryBuilder queryBuilder = QueryBuilders.termQuery("id", string);
			  
			  ElsConnection ec = ElasticFactory.getConnection();
			  List<JsonObject> list = ec.queryAllByQueryBuilder("xxx", "xxx", queryBuilder);
			  for (JsonObject jsonObject : list) {
				  System.err.println(jsonObject.toString());
				  FileHelper.writeFile(
						  "C:\\Users\\Administrator\\Desktop\\ODR服务资源数据\\22\\"+jsonObject.get("id").getAsString(), 
						  jsonObject.toString());
			  }
		  }
		  
		  
	  }
	  @Test
	  public void delete2(){
		 
		  String indexName = "xxx";
		 
			  
			  ElsConnection ec = ElasticFactory.getConnection();
			 
			 
					  boolean is = ec.deleteOne(indexName, indexName, "AVqS7Ublxgurc9r6WAD4");
					 
		System.err.println(is);
		  
	  }
	  @Test
	  public void updatData(){
		  String indexName = "xxx";
		  ElsConnection ec = ElasticFactory.getConnection();
		  QueryBuilder queryBuilder = QueryBuilders.termQuery("area", "西湖区");
		  List<JsonObject> list = ec.queryAllByQueryBuilder(indexName, indexName, queryBuilder);
		  CounselorDetail counselorDetail = null;
		  for (JsonObject jsonObject : list) {
			  String _id = jsonObject.get("_id").getAsString();
			  String lid = jsonObject.get("id").getAsString();
			   counselorDetail = counselorDetailDAO.get(Long.valueOf(lid));
//			  110 120 122 108 116 104 105 106
//			   AVqTDgJ6xgurc9r6WAEJ AVqTDgWuxgurc9r6WAEL AVqTE9ECxgurc9r6WAEN AVqTE9oRxgurc9r6WAER AVqTGShIxgurc9r6WAEV
//			   AVqTDf8Dxgurc9r6WAEH  AVqTE6uSxgurc9r6WAEM  AVqTE9Llxgurc9r6WAEO AVqTGSuexgurc9r6WAEX
			  if (counselorDetail !=null && lid.equals(String.valueOf(counselorDetail.getId()))) {
				  
				  jsonObject.remove("_id");
				  jsonObject.remove("_type");
				  jsonObject.addProperty("photo", "zixunshi_"+counselorDetail.getCounselor().getId());
				  jsonObject.addProperty("id", counselorDetail.getCounselor().getId());
				  String string = ec.insertOne(indexName, indexName, _id, jsonObject);
				  System.err.println(lid);
			 }else{
				 System.out.println(lid);
			 }
			 
		}
	  }
	  @Test
	  public void updatData2(){
		  String indexName = "xxx";
		  ElsConnection ec = ElasticFactory.getConnection();
		JsonObject jsonObject = ec.selectOne(indexName, indexName, "AVqTE6uSxgurc9r6WAEM", null);
		  CounselorDetail counselorDetail = null;
		  String _id = jsonObject.get("_id").getAsString();
		  String lid = jsonObject.get("id").getAsString();
		  counselorDetail = counselorDetailDAO.get(Long.valueOf(lid));
		  jsonObject.remove("_id");
		  jsonObject.remove("_type");
		  jsonObject.addProperty("photo", "zixunshi_"+counselorDetail.getCounselor().getId());
		  jsonObject.addProperty("id", counselorDetail.getCounselor().getId());
		  String string = ec.insertOne(indexName, indexName, _id, jsonObject);
		  System.err.println(string);
//		  for (JsonObject jsonObject : list) {
//			  110 120 122 108 116 104 105 106
//			   AVqTDgJ6xgurc9r6WAEJ AVqTDgWuxgurc9r6WAEL AVqTE9ECxgurc9r6WAEN AVqTE9oRxgurc9r6WAER AVqTGShIxgurc9r6WAEV
//			   AVqTDf8Dxgurc9r6WAEH  AVqTE6uSxgurc9r6WAEM  AVqTE9Llxgurc9r6WAEO AVqTGSuexgurc9r6WAEX
//			  if (counselorDetail !=null && lid.equals(String.valueOf(counselorDetail.getId()))) {
//				  
//				 
//				  System.err.println(lid);
//			  }else{
//				  System.out.println(lid);
//			  }
//			  
//		  }
	  }
	  /**
	   * 咨询是修改专业领域/所属地区
	   * @throws Exception
	   */
	  @Test
	  public void updateCounselors() throws Exception{
		  String indexName = "xxx";
		  ElsConnection ec = ElasticFactory.getConnection();
		  QueryBuilder queryBuilder = null;
//		  File file=new File("C:\\Users\\Administrator\\Desktop\\odr\\咨询师专业修改3.15（2）.xlsx");
		  File file=new File("C:\\Users\\Administrator\\Desktop\\odr\\ODR案例共享数据\\咨询师所在区域更新汇总表【3.17】.xls");
		  FileInputStream is=new FileInputStream(file);
		  HSSFWorkbook xxx = new HSSFWorkbook(is); 
		  HSSFSheet sheet = xxx.getSheetAt(0);
		  int trlength=sheet.getLastRowNum();//总行数
		 
//	   		HSSFRow row=sheet.getRow(0);
		  HSSFRow row=sheet.getRow(1);
//	   		int tdlength=row.getLastCellNum();//总列数
		  JsonObject jsonObject = null;

		  for (int i = 1118; i < trlength+1; i++) {	
			  //XSSFCell cell=sheet.getRow(i).getCell(13);
			  HSSFRow row2=sheet.getRow(i);
			  String name = row2.getCell(1).toString();
			  String phone = getCell(row2.getCell(10));
			  queryBuilder = QueryBuilders.termQuery("phone", phone);
			  List<JsonObject> list = ec.queryAllByQueryBuilder(indexName, indexName, queryBuilder);
			if (list!=null && list.size()==1) {
				  jsonObject = list.get(0);
				  String _id = jsonObject.get("_id").getAsString();
				  jsonObject.addProperty("status", "0");
//				  if (row2.getCell(7)==null) {
//					  jsonObject.addProperty("ability", "其他纠纷");//专业范围
//				  }else{
//					  jsonObject.add("ability", StringToJsonArray(row2.getCell(7).toString()));//专业范围
//				  }
				  jsonObject.addProperty("area", row2.getCell(8).toString().trim());
				  String photo = jsonObject.get("photo").getAsString().trim();
				  jsonObject.addProperty("photo", photo+".png");
				  jsonObject.remove("_id");
				  jsonObject.remove("_type");
				 String dd = ec.insertOne(indexName, indexName, _id, jsonObject);
				 System.err.println(dd);
			}else{
				System.err.println(name+"======="+phone);
			}
			 if (list.size()-1==i) {
				 System.err.println(name+"===完成===="+phone);
			 }
		  }
		  
		  
	  }
	  @Test
	  public void updateOne(){
		  String indexName = "zzz";
		  ElsConnection ec = ElasticFactory.getConnection();
		  QueryBuilder queryBuilder = QueryBuilders.termQuery("area", "其他");
		  List<JsonObject> list = ec.queryAllByQueryBuilder(indexName, indexName, queryBuilder);
		  JsonObject jsonObject = list.get(0);//AVqTwa2hxgurc9r6WAFv
//		  JsonObject jsonObject = ec.selectOne(indexName, indexName, "AVqTwa2hxgurc9r6WAFv", null);
		  //AVqTxnlYxgurc9r6WAW4   AVqTxnoZxgurc9r6WAW5
		  String _id = jsonObject.get("_id").getAsString();
		  jsonObject.remove("_id");
		  jsonObject.remove("_type");
//		  String str = "合同纠纷";
//		  String arr = "['刑事案件','建筑工程','交通事故','人身损害','劳动争议','婚姻继承','公司业务','房屋买卖']";
//		  JsonArray jsonArray = new Gson().fromJson(arr.toString(), JsonArray.class);
//		  JsonArray jsonArray = stringToJsonArray(str);
//		  jsonObject.add("ability", jsonArray);
//		  jsonObject.addProperty("status", "0");
//		  jsonObject.addProperty("phone", "17706538082/13067806782");
		  jsonObject.addProperty("area", "云南省昆明市");
		  String id = ec.insertOne(indexName, indexName, _id, jsonObject);
		  System.err.println(id);
		  
	  }
	  
	  @Test
	 public void xxxx(){
		  String str = "民间借贷、刑事案件,人身损害   、公司业务,  其他纠纷";
		  JsonArray array = stringToJsonArray(str);
		  System.err.println(array);
	 }
	  
	  
	  public static JsonArray stringToJsonArray(String string){
		  String[] arrStrings = string.split("、");
		  String temp = "[";
		  for (String str : arrStrings) {
			 String subStr = str.trim();
			  if (subStr.indexOf(",")>=0) {
				  String[] subArr = subStr.split(",");
				  for (String string2 : subArr) {
					  temp += "'"+string2.trim()+"',";
				  }
				  
				  
			  }else{
				  temp += "'"+str.trim()+"',";
			  }
		  }
		  temp = temp.substring(0, temp.lastIndexOf(","))+"]";
		  JsonArray jsonArray = new Gson().fromJson(temp, JsonArray.class);
		  return jsonArray;
		  
	  }
	  
	  @Test
	  public void dddOne(){
		  String indexName = "xxx";
		  ElsConnection ec = ElasticFactory.getConnection();
//		  QueryBuilder queryBuilder = QueryBuilders.termQuery("name", "邴朝祥");
		  boolean is = ec.deleteOne(indexName, indexName, "AVqTs8ojxgurc9r6WAEm");
		  System.err.println(is);
	  }
	 
	  
	
}
